package com.archive.cleaner.processor;

import com.archive.core.ArchiveContext;
import com.archive.core.util.ArchiveUtil;
import com.archive.core.util.SystemClock;
import lombok.Setter;
import org.slf4j.Logger;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashSet;
import java.util.Set;

@Setter
public class CleanProcessor {

    private Logger logger;
    private DataSource dataSource;

    public CleanProcessor(Logger logger) {
        this.logger = logger;
    }

    public void saveBackupTimeStamp(long now, String tableSuffix, DataSource dataSource) throws SQLException {
        Set<String> tableNames = new HashSet<>();
        tableNames.add("ks_cleaner_timestamp");
        Connection connection = null;
        PreparedStatement stmt = null;
        ResultSet resultSet = null;
        try {
            connection = dataSource.getConnection();
            stmt = connection.prepareStatement(ArchiveContext.getTableSql(connection.getCatalog(), tableNames));
            resultSet = stmt.executeQuery();
            if (!resultSet.next()) {
                String createTableSql = "CREATE TABLE ks_cleaner_timestamp (" +
                        " id BIGINT auto_increment NOT NULL PRIMARY KEY," +
                        " `timestamp` bigint(20) NOT NULL," +
                        " table_suffix varchar(100) NOT NULL" +
                        ") " +
                        " ENGINE=InnoDB " +
                        " DEFAULT CHARSET=utf8mb4 " +
                        " COLLATE=utf8mb4_general_ci;";
                PreparedStatement createTableStatement = connection.prepareStatement(createTableSql);
                createTableStatement.execute();
                ArchiveUtil.closeStatement(createTableStatement);
            }
            String sql = "insert into ks_cleaner_timestamp (`timestamp`, table_suffix) values(" +
                    "'" + now + "','" + tableSuffix + "'" +
                    ");";
            PreparedStatement insertStatement = connection.prepareStatement(sql);
            insertStatement.execute();
            ArchiveUtil.closeStatement(insertStatement);
        } finally {
            ArchiveUtil.close(connection, stmt, resultSet);
        }
    }

    public void clean(Integer deleteInterval) throws Exception {
        logger.info("start clean tables, deleteInterval {}", deleteInterval);
        long deleteTimestamp = SystemClock.now() - deleteInterval * 24 * 60 * 60 * 1000L;
        StringBuilder sql = new StringBuilder("select * from ks_cleaner_timestamp where timestamp <= ").append(deleteTimestamp);

        try (Connection connection = dataSource.getConnection();
             Statement stmt = connection.createStatement();
             ResultSet resultSet = stmt.executeQuery(sql.toString())) {
            boolean clean = false;
            while (resultSet.next()) {
                sql.setLength(0);
                String tableSuffix = resultSet.getString("table_suffix");
                long id = resultSet.getLong("id");
                long timestamp = resultSet.getLong("timestamp");
                logger.info("delete timestamp {}, {}, {}", id, timestamp, tableSuffix);
                printSplitLine();
                sql = new StringBuilder("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '");
                sql.append(connection.getCatalog()).append("' and TABLE_NAME like '%").append(tableSuffix).append("'");
                try (Statement tableStatement = connection.createStatement();
                     ResultSet tableResultSet = tableStatement.executeQuery(sql.toString())) {
                    while (tableResultSet.next()) {
                        String tableName = tableResultSet.getString(1);
                        sql.setLength(0);
                        sql.append("drop table ").append(tableName).append(";");
                        try (Statement dropStatement = connection.createStatement()) {
                            dropStatement.execute(sql.toString());
                        }
                        logger.info("drop table {}", tableName);
                        if (!clean) {
                            clean = true;
                        }
                    }
                }
                printSplitLine();
                sql.setLength(0);
                sql.append("delete from ks_cleaner_timestamp where id = ").append(id);
                try (Statement deleteStatement = connection.createStatement()) {
                    deleteStatement.execute(sql.toString());
                }
            }
            if (!clean) {
                logger.info("there is no table to clean.");
            }
        }

        logger.info("clean table finish.");
    }

    private void printSplitLine() {
        logger.info("----------------------------------------------------------");
    }

    public void listTimestamp() {
        boolean clean = false;
        logger.info("The backup timestamp and tableSuffix:");
        String sql = "select * from ks_cleaner_timestamp";
        try (Connection connection = dataSource.getConnection();
             Statement stmt = connection.createStatement();
             ResultSet resultSet = stmt.executeQuery(sql)) {
            int index = 1;
            while (resultSet.next()) {
                long timestamp = resultSet.getLong("timestamp");
                String tableSuffix = resultSet.getString("table_suffix");
                logger.info("{}: {} {}", index++, ArchiveUtil.formatTime(timestamp, "yyyy-MM-dd HH:mm:ss"), tableSuffix);
                if (!clean) {
                    clean = true;
                }
            }
        } catch (Exception e) {
        } finally {
            if (!clean) {
                logger.info("backup timestamp is empty.");
            }
        }
    }

    public void listTablesWithSuffix(String tableSuffix) {
        boolean clean = false;
        logger.info("the backup tables with specified suffix {}:", tableSuffix);
        StringBuilder sql = new StringBuilder("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = '");
        try (Connection connection = dataSource.getConnection();
             Statement stmt = connection.createStatement();
             ResultSet resultSet = stmt.executeQuery(sql.append(connection.getCatalog()).append("' and TABLE_NAME like '%").append(tableSuffix).append("'").toString())) {
            int index = 1;
            while (resultSet.next()) {
                String tableName = resultSet.getString(1);
                logger.info("{}: {}", index++, tableName);
                if (!clean) {
                    clean = true;
                }
            }
        } catch (Exception e) {
        } finally {
            if (!clean) {
                logger.info("tables with specified suffix {} is empty.", tableSuffix);
            }
        }
    }
}
